/*
 * $RCSfile: Postgres2Oracle.java,v $
 * $Revision: 1.8 $
 *
 * Comments:
 *
 * (C) Copyright Biuro-Technika 2007.  All rights reserved.
 * THIS IS UNPUBLISHED PROPRIETARY SOURCE CODE OF Biuro-Technika
 * The copyright notice above does not evidence any
 * actual or intended publication of such source code.
 *
 * $Author: michal $          $Locker:  $
 * $Date: 2010/05/24 10:43:28 $
 * $Log: Postgres2Oracle.java,v $
 * Revision 1.8  2010/05/24 10:43:28  michal
 * parametryzacja kodu
 *
 * Revision 1.7  2009/07/17 12:56:09  michal
 * eliminacja migracji schem systemowych
 *
 * Revision 1.6  2009/05/15 08:27:42  michal
 * zamkniecie obiektow ResultSet,Statement w getSchemas
 *
 * Revision 1.5  2009/01/02 13:32:33  michal
 * zmiana statycznej nazwy schemy centralnej na dynamiczna
 *
 * Revision 1.4  2007/09/20 09:18:28  dominik
 * Usuniecie zbednych importow
 *
 * Revision 1.3  2007/08/22 12:26:42  muzzy
 * niepotrzebny import
 *
 * Revision 1.2  2007/08/21 09:48:30  dominik
 * Poprawki pod jave 1.4
 *
 * Revision 1.1  2007/08/21 09:20:58  dominik
 * Migracja bazy postgresql do oracle
 *
 */


package parasoft.centrap.migration;

import java.io.File;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Hashtable;
import java.util.Vector;

import parasoft.centrap.connection.DbConnection;
import parasoft.centrap.connection.DebuggablePreparedStatement;
import parasoft.centrap.connection.OracleConnection;
import parasoft.centrap.db.DbException;

public class Postgres2Oracle {

	public final static String POSTGRES_PROPERTIES = "POSTGRES_PROPERTIES";
        public final static String ORACLE_PROPERTIES = "ORACLE_PROPERTIES";
        public final static String PROPERTIES_PREFIX = "centrap.";
        public final static String DBMANAGER = PROPERTIES_PREFIX + "dbmanager";

	private final DbConnection postgresConnection = null;

	private final OracleConnection oracleConnection = null;

	//oid -> Schema, Table or Column
	private final Hashtable<Long, Object> dbElements = new Hashtable<Long, Object>();
	private final Vector<Schema> structure = new Vector<Schema>();

	public Postgres2Oracle() {
                System.out.println("Initializing...");
                try {
                        final String PATH = System.getProperty(POSTGRES_PROPERTIES, "./") +"postgres.properties";
                        System.out.println(PATH);
                        final File props = new File(PATH);
                        if(!props.exists()){
                                System.err.println("File "+PATH+" not exist!!");
                                System.exit(1);
                        }

			System.err.print("Initializing DbManager...");
			System.err.print("OK\n");

			System.err.print("OK\n");
		}
		catch (final UnsatisfiedLinkError e) {
		try {
			System.out.println(System.getProperty(ORACLE_PROPERTIES, "../") +"oracle.properties");

			System.err.print("Initializing DbManager...");
			System.err.print("OK\n");

		}
		catch (final Exception ex) {
			System.err.println("Error initializing dbmanager: " + ex);
		}
		}


	}

	public void connectToPostgres()
	{

		System.err.print("Getting connection...");
		System.err.print("OK\n");
	}

	public void connectToOracle()
	{

		System.err.print("Getting connection...");
		System.err.print("OK\n");
	}

	public void getStructure()
	{
		try {
			System.out.println("Pobieram strukture bazy danych...");
			getSchemas();
			getTables();
			getColumns();
			System.out.println("OK");

		} catch(final SQLException e){
			e.printStackTrace();
		}
	}

        private void getSchemas() throws SQLException
        {
                System.out.print("Pobieram schemy...");
                final String query = "SELECT NSPNAME, OID ,NSPOWNER,USESYSID FROM PG_NAMESPACE, PG_SHADOW WHERE (USENAME = ? OR USENAME = ?) AND NSPOWNER = USESYSID";
                final PreparedStatement stmt = postgresConnection.prepareStatement(query);
                stmt.setString(2, "magmaster");

                final ResultSet rs = stmt.executeQuery();
                while (rs.next()){
                        final String schema_name = rs.getString("NSPNAME");
                        final String nspowner = rs.getString("NSPOWNER");
                        final String usesysid = rs.getString("USESYSID");
                        if(!nspowner.equals(usesysid)){
                                continue;
                        }
                        final long oid = rs.getLong("OID");
                        final Schema schema = new Schema(oid, schema_name);
                        structure.add(schema);
                        dbElements.put(new Long(oid), schema);
                }
                postgresConnection.closeStatement(rs, stmt);
                System.out.println("OK");
        }

	private void getTables()
	{
		System.out.print("Pobieram tabele...");
		try {
			for (final Schema schema : structure) {
				final long oid = schema.getOid();
				final String query = "SELECT OID, RELNAME, USENAME FROM PG_CLASS, PG_SHADOW WHERE RELNAMESPACE = ? AND RELKIND = 'r' AND RELOWNER = USESYSID";
				final PreparedStatement stmt = postgresConnection.prepareStatement(query);
				stmt.setLong(1, oid);
				final ResultSet rs = stmt.executeQuery();
				while (rs.next()){
					final long columnOid = rs.getLong("OID");
					final String tableName = rs.getString("RELNAME");
					final String owner = rs.getString("USENAME");
					final Table table = new Table(columnOid, tableName, owner);
					schema.addTable(table);
					dbElements.put(new Long(oid), table);
				}
			}
		} catch(final SQLException e){
			e.printStackTrace();
		}
		System.out.println("OK");
	}

	private void getColumns()
	{
		System.out.print("Pobieram kolumny...");
		try {
			for (final Schema schema : structure) {
				final Vector<Table> tables = schema.getTables();

				for (final Table table : tables) {
					final long oid = table.getOid();

					//wybieramy kolumny z typem danych, defaultowymi warto�ciami i czy s� 'not null'
					final String query = "SELECT ATTNAME, PG_CATALOG.FORMAT_TYPE(ATTTYPID, ATTTYPMOD) AS TYPE, ATTNOTNULL, "+
					"(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) FROM pg_catalog.pg_attrdef d "+
					"WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) AS DEFAULT "+
					"FROM PG_ATTRIBUTE a, PG_TYPE WHERE ATTRELID = ? AND ATTNUM > 0 AND ATTTYPID = PG_TYPE.OID ";

					final PreparedStatement stmt = postgresConnection.prepareStatement(query);
					stmt.setLong(1, oid);

					final ResultSet rs = stmt.executeQuery();
					while (rs.next()){
						final String columnName = rs.getString("ATTNAME");
						String type = rs.getString("TYPE");

						type = convertType(type);

						if(rs.getString("DEFAULT").length()>0){
							type += " DEFAULT "+rs.getString("DEFAULT");
						}
						if (rs.getBoolean("ATTNOTNULL")){
							type += " NOT NULL";
						}
						final Column column = new Column(columnName,  type);
						table.addColumn(column);
					}
				}

				//sprawdzamy czy dana kolumna jest kluczem g��wnym (na podstawie definicji indexu - sprawdzamy czy ma w nazwie s�owo key)
				final String pkeyQuery = "SELECT TABLENAME, INDEXDEF FROM PG_INDEXES WHERE INDEXDEF LIKE '%key%' AND SCHEMANAME = '"+schema.getSchemaName()+"'";
				final ResultSet pkeyResult = postgresConnection.createStatement().executeQuery(pkeyQuery);
				String pkey = "";
				while (pkeyResult.next()) {
					final String indexdef = pkeyResult.getString("INDEXDEF");
					pkey = indexdef.substring(indexdef.indexOf("(")+1, indexdef.indexOf(")"));
                                        final String tableName = pkeyResult.getString("TABLENAME");

                                        final Column column = schema.getTable(tableName).getColumn(pkey);
                                        if(column!=null){
                                                column.setPrimaryKey(true);
                                        }
                                }
                        }
                } catch(final SQLException e){
                        e.printStackTrace();
                }
		System.out.println("OK");
	}

	public void createStructure()
	{
		try {
			System.out.println("Zaczynam tworzy� strukture schem...");
			createSchemas();
			createTables();
			System.out.println("OK");
		} catch (final SQLException e) {
			e.printStackTrace();
		}
	}

	public void createSchemas() throws SQLException
	{
		System.out.println("Tworze scheme...");
		for(int i =0; i<structure.size();i++ ) {
			final Schema schema = structure.get(i);
			if(oracleConnection.existsSchema(schema.getSchemaName())) {
				System.out.println("Schemat "+schema.getSchemaName()+" juz istnieje - pomijam go!");
			} else {
				createSchema(schema.getSchemaName());
			}
		}
		System.out.println("OK");
	}

	private void createSchema(final String schema)
	{
		try {
			oracleConnection.createSchema(schema);
			System.out.println("Schemat: "+schema+" zosta� utworzony");
		} catch (final SQLException e) {
			e.printStackTrace();
		}
	}

	public void createTables()
	{
		System.out.print("Tworze tabele...");
		for (final Schema schema : structure) {
			for (final Table table : schema.getTables()) {
				int i = 0;
				final int columnsNumber = table.getColumns().size();
				String fields = "(";
				String key = "";
				for (final Column column : table.getColumns()) {
					fields += column.getColumnName() + " " +column.getType();

					if (column.isPrimaryKey()){
						key = column.getColumnName();
						fields += " PRIMARY KEY";
					}
					i++;
					if (i < columnsNumber){
						fields += ", ";
					}
				}
				fields += ")";
				try {
//					System.out.println("CREATE TABLE " + schema.getSchemaName()+"."+table.getTableName() + " " + fields);
					oracleConnection.createTable(schema.getSchemaName()+"."+table.getTableName(), fields);

					if (key.length() > 0){
//						System.out.println("CREATE SEQUENCE " + schema.getSchemaName()+"." + key +
//							" START WITH 1 INCREMENT BY 1" +
//							" MINVALUE 1 NOMAXVALUE NOCYCLE ORDER");
						oracleConnection.createSequence(schema.getSchemaName()+"."+key);
					}
				} catch (final Exception e) {
					e.printStackTrace();
				}
			}
		}
		System.out.print("OK");
	}

	private Vector<String> getIndexes()
	{
		System.out.print("Pobieram indexy...");
		final Vector<String> indexes = new Vector<String>();
		DebuggablePreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			for (final Schema schema : structure) {
				final String query = "SELECT INDEXDEF FROM PG_INDEXES WHERE SCHEMANAME = ?";
				stmt = postgresConnection.prepareStatement(query);
				stmt.setString(1, schema.getSchemaName());

				rs = stmt.executeQuery();
				while (rs.next()){
					String index_def = rs.getString("INDEXDEF");

					index_def = index_def.toLowerCase().replaceAll("using btree", "");
					index_def = index_def.replaceAll("::text", "");

					indexes.add(index_def);
				}
			}
		} catch (final SQLException e1) {
			e1.printStackTrace();
		} finally {
			postgresConnection.closeStatement(rs, stmt);
		}
		return indexes;
	}

	private void createIndexes(final Vector<String> indexes)
	{
		System.out.print("Tworze indexy...");
		for (final String index : indexes) {
			DebuggablePreparedStatement stmt = null;
			try {
				System.out.print("Tworze index: "+index);

				stmt = oracleConnection.prepareStatement(index);
				stmt.executeUpdate();

				System.out.println(" ...OK");
			} catch(final Exception e){
				System.err.println(" ...BLAD!!!");
				e.printStackTrace();
			} finally {
				oracleConnection.closeStatement(stmt);
			}
		}
		System.out.print("OK");
	}

	private String convertType(String type) {
		type = type.replaceAll("character varying",	 "varchar2");
		type = type.replaceAll("character",	 "char");
		type = type.replaceAll("smallint",	 "number(38)");
		type = type.replaceAll("bigint",	 "number(38)");
		type = type.replaceAll("timestamp without time zone",	 "date");
		type = type.replaceAll("timestamp",	 "date");
		type = type.replaceAll("integer",	 "number(38)");
		type = type.replaceAll("double precision",	 "float(126)");
		type = type.replaceAll("real",	 "float(126)");
		type = type.replaceAll("boolean",	 "number(1)");
		type = type.replaceAll("text",	 "varchar2(4000)");

		return type;
	}


//	public void wypiszSchemat(){
//		for(Enumeration e = structure.elements(); e.hasMoreElements();){
//			Schema s = (Schema) e.nextElement();
//			s.wypisz();
////			System.out.println(tableName+"  "+s.getColumnName());
//		}
//	}

	public void setStatement(final DebuggablePreparedStatement stmt, final int index, Object value, final int type)
	throws SQLException, DbException
	{
	    switch(type) {
	    	case Types.BIGINT:
        	case Types.NUMERIC:
        		if(value==null) { value = new Long(0); }
	            stmt.setLong(index, ((Long)value).longValue());
	            break;

	        case Types.INTEGER:
        		if(value==null) { value = new Integer(0); }
	            stmt.setInt(index, ((Integer)value).intValue());
	            break;

	        case Types.SMALLINT:
        		if(value==null) { value = new Short((short) 0); }
	            stmt.setShort(index, ((Short)value).shortValue());
	            break;

	        case Types.CHAR:
	        case Types.VARCHAR:
        		if(value==null) { value = new String(""); }
	                stmt.setString(index, ((String)value).toUpperCase());
	            break;

	        case Types.REAL:
	        case Types.FLOAT:
	        case Types.DOUBLE:
        		if(value==null) { value = new Double(0); }
	            stmt.setDouble(index, ((Double)value).doubleValue());
                    break;

                case Types.BOOLEAN:
                        if(value==null) { value = Boolean.valueOf(false); }
                    stmt.setBoolean(index, ((Boolean)value).booleanValue());
                    break;

	        case Types.DATE:
//        		if(value==null) { value = new java.sql.Date(Calendar.getInstance().getTime().getTime()); }
	            stmt.setDate(index, ((Date)value));
	            break;

	        case Types.TIMESTAMP:
//        		if(value==null) { value = new Timestamp(Calendar.getInstance().getTime().getTime()); }
	            stmt.setTimestamp(index, ((Timestamp)value));
	            break;

	        default:
	            throw new DbException("Unknown object type! "+ type );
	    }
	}

	public void moveData(final String schema, final String table)
	{
		try {
			String query = "SELECT COUNT(*) AS ROWS FROM " + schema + "." + table;
			ResultSet rs = postgresConnection.createStatement().executeQuery(query);
			long rows = 0;
			if(rs.next()) {
				rows = rs.getLong(1);
			}

			final long offsetValue = 1000; //wartosc o ktora bedziemy przesuwac limit - zabezpieczenie przed bledem braku pamieci
			if(rows > offsetValue) {
				final String limit = " LIMIT "+ offsetValue;
				final String offset = " OFFSET ";
				for(int j=0; j*offsetValue < rows; j++) {

					query = "SELECT * FROM " + schema + "." + table;
					if(j==0) {
						query += limit;
					}
					else {
						query += limit + offset + j*offsetValue;
					}

					System.out.println(query);

					rs = postgresConnection.createStatement().executeQuery(query);
					final ResultSetMetaData metaData = rs.getMetaData();

					final int columnCount = metaData.getColumnCount();
					String insertInto = "INSERT INTO " + schema + "." + table + "(" +metaData.getColumnName(1);

					for (int i = 2; i < columnCount+1; i++) {
						insertInto += ", "+metaData.getColumnName(i);
					}
					insertInto += ") VALUES (";
					while (rs.next()){
						String insert = insertInto + " ? ";
						for (int i = 2; i < columnCount+1; i++) {
							insert += ",? ";
						}
						insert += ")";

						DebuggablePreparedStatement stmt = null;
						oracleConnection.startTransaction();
						try {
							stmt = this.oracleConnection.prepareStatement(insert);

							int param=1;
							for (int i = 1; i < columnCount+1; i++) {
								setStatement(stmt,param++,rs.getObject(i),metaData.getColumnType(i));
							}
							stmt.executeUpdate();

						} finally {
							oracleConnection.closeStatement(stmt);
							oracleConnection.finishTransaction();
						}
					}
					postgresConnection.closeAllStatements();
				}
			}
			else {
				query = "SELECT * FROM " + schema + "." + table;

				System.out.println(query);

				rs = postgresConnection.createStatement().executeQuery(query);
				final ResultSetMetaData metaData = rs.getMetaData();

				final int columnCount = metaData.getColumnCount();
				String insertInto = "INSERT INTO " + schema + "." + table + "(" +metaData.getColumnName(1);

				for (int i = 2; i < columnCount+1; i++) {
					insertInto += ", "+metaData.getColumnName(i);
				}
				insertInto += ") VALUES (";
				while (rs.next()){
					String insert = insertInto + " ? ";
					for (int i = 2; i < columnCount+1; i++) {
						insert += ",? ";
					}
					insert += ")";

					DebuggablePreparedStatement stmt = null;
					oracleConnection.startTransaction();
					try {
						stmt = this.oracleConnection.prepareStatement(insert);

						int param=1;
						for (int i = 1; i < columnCount+1; i++) {
							setStatement(stmt,param++,rs.getObject(i),metaData.getColumnType(i));
						}
						stmt.executeUpdate();

					} finally {
						oracleConnection.closeStatement(stmt);
						oracleConnection.finishTransaction();
					}
				}
				postgresConnection.closeAllStatements();
			}
		} catch (final SQLException e) {
			e.printStackTrace();
		} catch (final DbException e) {
			e.printStackTrace();
		}
	}

	public void moveData()
	{
		for(int i=0;i<structure.size();i++) {
			final Schema schema = structure.get(i);
			final Vector<Table> tables = schema.getTables();
                        for(int j=0;j<tables.size();j++) {
                                final Table table = tables.get(j);
                                moveData(schema.getSchemaName(), table.getTableName());
                                System.out.println("Table "+(j+1)+"/"+tables.size());
                        }
                        System.out.println("Schema "+(i+1)+"/"+structure.size());
                }
        }

	private void destroy()
	{
	}

	public static void main(final String args[])
	{
		final Postgres2Oracle migration = new Postgres2Oracle();
		migration.connectToPostgres();
		migration.connectToOracle();

		//pobieram i tworze strukture bazy danych
		migration.getStructure();
		migration.createStructure();

		//przenosze dane
		migration.moveData();

		//zakladam indeksy
		final Vector<String> indexes = migration.getIndexes();
		migration.createIndexes(indexes);

		System.out.println("=========================");
		migration.destroy();

	}
}
